package com.mbc.common.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mbc.common.entities.CurrencyRate;
import com.mbc.common.entities.CurrencyRateRpt;
import com.mbc.common.data.CurrencyRateDataList;

public class CurrencyRateDAOImpls extends DAO<CurrencyRate> implements
		CurrencyRateDAO {

	public CurrencyRateDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "CurrencyRate";
	}

	@Override
	public void create(CurrencyRate entity) throws SQLException {
		query = "insert into "
				+ table_name
				+ " ( CurrencyRateId, CurrencyRateCode, Description, SourceCurrencyId, ConvertCurrencyId, Roper, BranchId) values( ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getCurrencyRateId());
		pstmt.setString(i++, entity.getCurrencyRateCode());
		pstmt.setString(i++, entity.getDescription());
		pstmt.setLong(i++, entity.getSourceCurrencyId());
		pstmt.setLong(i++, entity.getConvertCurrencyId());
		pstmt.setString(i++, entity.getRoper());
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void update(CurrencyRate entity) throws SQLException {
		query = "update "
				+ table_name
				+ " set  CurrencyRateCode = ?, Description = ?, SourceCurrencyId = ?, ConvertCurrencyId = ?, Roper = ?"
				+ " where currencyrateid = " + entity.getCurrencyRateId();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setString(i++, entity.getCurrencyRateCode());
		pstmt.setString(i++, entity.getDescription());
		pstmt.setLong(i++, entity.getSourceCurrencyId());
		pstmt.setLong(i++, entity.getConvertCurrencyId());
		pstmt.setString(i++, entity.getRoper());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where currencyrateid = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public CurrencyRate find(long key) throws SQLException {
		CurrencyRate l_CurrencyRate = null;
		query = getSelectQuery() + " where currencyrateid = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_CurrencyRate = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_CurrencyRate;
	}

	@Override
	public ArrayList<CurrencyRate> findAll() throws SQLException {
		ArrayList<CurrencyRate> l_CurrencyRateList = new ArrayList<CurrencyRate>();
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_CurrencyRateList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_CurrencyRateList;
	}

	@Override
	protected CurrencyRate readRecord(ResultSet rs) throws SQLException {
		CurrencyRate l_entity = new CurrencyRate();
		l_entity.setCurrencyRateId(rs.getLong("CurrencyRateId"));
		l_entity.setCurrencyRateCode(rs.getString("CurrencyRateCode"));
		l_entity.setDescription(rs.getString("Description"));
		l_entity.setSourceCurrencyId(rs.getLong("SourceCurrencyId"));
		l_entity.setConvertCurrencyId(rs.getLong("ConvertCurrencyId"));
		l_entity.setRoper(rs.getString("Roper"));
		l_entity.setBranchId(rs.getLong("BranchId"));
		return l_entity;
	}

	public CurrencyRateDataList getList(CurrencyRate cri) throws SQLException {
		CurrencyRateDataList l_DataList = new CurrencyRateDataList();
		ArrayList<CurrencyRate> l_entityList = new ArrayList<CurrencyRate>();
		/*String l_cri = " and (CurrencyRateCode like N'%" + cri.getKeyword()
		+ "%'" + " or Description like N'%" + cri.getKeyword() + "%')"
		+ " and BranchId = " + cri.getBranchId();*/

		String l_cri = " and (" + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%') and BranchId = " + cri.getBranchId() ;

		query = "set rowcount "
				+ cri.getSize()
				+ " select * from (select row_number() over(order by CurrencyRateId desc) as [rowno], * from "
				+ table_name + " where 1 = 1" + l_cri + ") " + table_name
				+ " where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				l_entityList.add(readRecord(rs));
			} while (rs.next());
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		query = "select count(CurrencyRateId) from " + table_name
				+ " where 1 = 1" + l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	@Override
	public CurrencyRate findByCode(String code, long branchId)
			throws SQLException {
		CurrencyRate l_CurrencyRate = null;
		query = getSelectQuery() + " where currencyratecode = N'" + code
				+ "' and BranchId = " + branchId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_CurrencyRate = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_CurrencyRate;
	}

	@Override
	public CurrencyRate find(long sourceCurrencyId, long convertCurrencyId,long branchId)
			throws SQLException {
		CurrencyRate l_CurrencyRate = null;
		query = "select top(1)* from " + table_name
				+ " where SourceCurrencyId = " + sourceCurrencyId
				+ " and ConvertCurrencyId = " + convertCurrencyId
				+ " and BranchId = " + branchId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_CurrencyRate = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_CurrencyRate;
	}

	// MBC(6/8/2014)
	public ResultSet prepareCurrencyRateReport(CurrencyRateRpt info)
			throws SQLException {
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_CurrencyRateRpt ?, ?, ?, ?, ?, ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		CurrencyRateRptParameter(l_pstmt, info);
		l_rs = l_pstmt.executeQuery();
		return l_rs;

	}

	private static void CurrencyRateRptParameter(CallableStatement l_pstmt,
			CurrencyRateRpt info) throws SQLException {
		int i = 1;

		l_pstmt.setString(i++, info.getFromCurrencyRateID());
		l_pstmt.setString(i++, info.getToCurrencyRateID());
		l_pstmt.setString(i++, info.getFromSourceCurrencyID());
		l_pstmt.setString(i++, info.getToSourceCurrencyID());
		l_pstmt.setString(i++, info.getFromConvertCurrencyID());
		l_pstmt.setString(i++, info.getToConvertCurrencyID());
		l_pstmt.setLong(i++, info.getBranchId());
	}

	public CurrencyRate getCurrencyRateFromSourcetoHome(long sourceCurrencyId,
			long branchId) throws SQLException {
		CurrencyRate l_CurrencyRate = null;
		query = " declare @SourceCurrencyID bigint "
				+ " set @SourceCurrencyID = "
				+  sourceCurrencyId 
				+ " declare @BranchID bigint "
				+ " set @BranchID = "
				+  branchId 
				+ " declare @HomeCurrencyID bigint "
				+ " set @HomeCurrencyID = (select HomeCurrencyID from setup) "
				+ " declare @CurrencyCode varchar(50) "
				+ "declare @count integer "
				+ " set @count = 0 "
				+ " set @count = (select count(1) from  CurrencyRate cr " 
				+ " left join CurrencyRateDetail crd on cr.CurrencyRateId=crd.CurrencyRateId "  
				+ " where cr.SourceCurrencyId = @SourceCurrencyID "
				+ " and  cr.ConvertCurrencyId = @HomeCurrencyID "
				+ " and BranchId  =  @BranchID) "
				+ " if(@count = 0 or @SourceCurrencyID = @HomeCurrencyID ) "
				+ " begin "
				+ " select @CurrencyCode=CurrencyCode from Currency where CurrencyId=@HomeCurrencyID "
				+ " select 0 CurrencyRateId,@CurrencyCode + @CurrencyCode CurrencyRateCode,1 Rate, @SourceCurrencyID SourceCurrencyID , @HomeCurrencyID ConvertCurrencyID"
				+ " end "
				+ " else  "
				+ " begin "
				+ " select top(1) crd.CurrencyRateId,cr.CurrencyRateCode,crd.Rate," 
				+ " cr.sourcecurrencyid, cr.convertcurrencyid from "
				+ " CurrencyRate cr "
				+ " left join CurrencyRateDetail crd on cr.CurrencyRateId=crd.CurrencyRateId "
				+ " where cr.SourceCurrencyId = @SourceCurrencyID " + " and "
				+ " cr.ConvertCurrencyId = @HomeCurrencyID "
				+ " and BranchId  = @BranchID "
				+ " order by crd.CurrencyRateDetailId desc " + " end ";
	
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_CurrencyRate = readRecords(rs);
		rs.close();
		pstmt.close();
		return l_CurrencyRate;
	}

	protected CurrencyRate readRecords(ResultSet rs) throws SQLException {
		CurrencyRate l_entity = new CurrencyRate();
		l_entity.setCurrencyRateId(rs.getLong("CurrencyRateId"));
		l_entity.setCurrencyRateCode(rs.getString("CurrencyRateCode"));
		l_entity.setSourceCurrencyId(rs.getLong("SourceCurrencyId"));
		l_entity.setConvertCurrencyId(rs.getLong("ConvertCurrencyId"));
		l_entity.setRate(rs.getDouble("Rate"));
		return l_entity;
	}
	// MBC End

	@Override
	public CurrencyRate findCurrencyRateByCode(String code) throws SQLException {
		CurrencyRate l_CurrencyRate = null;
		query = getSelectQuery() + " where CurrencyRateCode = N'" + code + "'";
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_CurrencyRate = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_CurrencyRate;
	}

}
